﻿using FRMS.Helper;
using FRMS.Model;
using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace FRMS.DAL
{
    public class ProjectDAL
    {
        public bool AddProject(ProjectInfo info)
        {
            string strsql = string.Format("INSERT INTO project_info (project_code,project_name,project_addr,apply_type,Isdefault,MenuRole)VALUES('{0}','{1}','{2}',{3},{4},'{5}')", info.project_code, info.project_name, info.project_addr, info.apply_type, info.Isdefault, info.MenuRole);
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }
        public bool EditProject(ProjectInfo info)
        {
            string strsql;
            strsql = string.Format("UPDATE project_info SET project_name='{1}',project_addr='{2}',apply_type={3},Isdefault={4} WHERE ID={5}", info.project_code, info.project_name, info.project_addr, info.apply_type, info.Isdefault, info.ID);

            if (info.Isdefault == 1)
            {
                strsql += ";UPDATE project_info SET Isdefault=0 WHERE ID!=" + info.ID;
            }
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }
        public List<ProjectInfo> QueryProject(string username, ProjectInfo info)
        {
            string strsql = "SELECT * FROM vi_project";
            string sqlwhere = string.Empty;
            if (username != "fstar")
            {
                sqlwhere += " ID=" + info.ID + " AND";
            }
            if (!string.IsNullOrEmpty(info.project_code))
            {
                sqlwhere += " project_code LIKE '%" + info.project_code + "%' AND";
            }
            if (!string.IsNullOrEmpty(info.project_name))
            {
                sqlwhere += " project_name LIKE '%" + info.project_name + "%' AND";
            }
            if (!string.IsNullOrEmpty(info.project_addr))
            {
                sqlwhere += " project_addr LIKE '%" + info.project_addr + "%' AND";
            }
            if (info.apply_type > 0)
            {
                sqlwhere += " apply_type=" + info.apply_type + " AND";
            }
            if (sqlwhere.Length > 0)
            {
                strsql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4) + " AND IsDelete=0";
            }
            else
            {
                strsql += " where IsDelete=0";
            }
            if (!string.IsNullOrEmpty(info.Orderby))
            {
                strsql += " ORDER BY " + info.Orderby;
            }
            if (info.Recordhead > -1 && info.Recordtail > 0)
            {
                strsql += " LIMIT " + info.Recordhead + "," + info.Recordtail;
            }

            return SqlHelper<ProjectInfo>.SqlList(strsql);
        }
        public int QueryProjectCount(string username, ProjectInfo info)
        {
            string strsql = "SELECT COUNT(1) FROM vi_project";
            string sqlwhere = string.Empty;

            if (username != "fstar")
            {
                sqlwhere += " ID=" + info.ID + " AND";
            }
            if (!string.IsNullOrEmpty(info.project_code))
            {
                sqlwhere += " project_code LIKE '%" + info.project_code + "%' AND";
            }
            if (!string.IsNullOrEmpty(info.project_name))
            {
                sqlwhere += " project_name LIKE '%" + info.project_name + "%' AND";
            }
            if (!string.IsNullOrEmpty(info.project_addr))
            {
                sqlwhere += " project_addr LIKE '%" + info.project_addr + "%' AND";
            }
            if (info.apply_type > 0)
            {
                sqlwhere += " apply_type=" + info.apply_type + " AND";
            }
            if (sqlwhere.Length > 0)
            {
                strsql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            else
            {
                strsql += " where IsDelete=0";
            }

            return SqlHelper<ProjectInfo>.SqlScalar(strsql);
        }
        public List<ApplyType> QueryApplyType()
        {
            string strsql = "SELECT * FROM apply_info";
            return SqlHelper<ApplyType>.SqlList(strsql);
        }
        public bool DeleteProject(int id)
        {
            //string strsql = "UPDATE project_info set IsDelete=1 WHERE ID=" + id;
            string strsql = string.Format(@"DELETE FROM controllerstate_record WHERE DeviceID in (SELECT ID FROM device_info WHERE projectID={0});DELETE FROM device_record WHERE DeviceID in (SELECT ID FROM device_info WHERE projectID={0});
                                            DELETE FROM ladder_records WHERE DeviceID in (SELECT ID FROM device_info WHERE projectID={0});DELETE FROM workmodestate_record WHERE DeviceID in (SELECT ID FROM device_info WHERE projectID={0});
                                            UPDATE building_info SET IsDelete=1 WHERE projectID={0};UPDATE device_info SET IsDelete=1 WHERE projectID={0};UPDATE elevator_info SET IsDelete=1 WHERE projectID={0};
                                            UPDATE owner_info SET IsDelete=1 WHERE projectID={0};UPDATE room_info SET IsDelete=1 WHERE projectID={0};UPDATE user_info SET IsDelete=1 WHERE projectID={0};
                                            UPDATE voucher_info SET IsDelete=1 WHERE projectID={0};UPDATE project_info SET IsDelete=1 WHERE ID={0}", id);
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }
        public ProjectInfo LoadProject(int id)
        {
            string strsql = "SELECT * FROM vi_project WHERE ID=" + id;
            return SqlHelper<ProjectInfo>.SqlSingleData(strsql);
        }
        public bool BindMenu(string menurole, int proid)
        {
            string strsql = "UPDATE project_info SET MenuRole='" + menurole + "' WHERE ID=" + proid;
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }
        public bool SwitchProject(int id)
        {
            string strsql = "UPDATE project_info SET Isdefault=1 WHERE ID=" + id + ";UPDATE project_info SET Isdefault=0 WHERE ID!=" + id;
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }
        public ProjectInfo QueryDefaultProject()
        {
            string strsql = "SELECT * FROM vi_project WHERE Isdefault=1 AND IsDelete=0 LIMIT 1";

            return SqlHelper<ProjectInfo>.SqlSingleData(strsql);
        }

        public bool CheckProID(string code)
        {
            string strSql = "SELECT COUNT(1) FROM project_info WHERE project_code='" + code + "' AND IsDelete=0";
            int count = SqlHelper<OwnerInfo>.SqlScalar(strSql);

            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int QueryMinProjectID(int project)
        {
            string strsql = "SELECT ID FROM vi_project WHERE IsDelete=0 and ID!=" + project + " order by ID asc limit 1";

            return SqlHelper<ProjectInfo>.SqlScalar(strsql);
        }

        public bool UpdateProjectSession(string session, int projectid)
        {
            string strsql;
            strsql = string.Format("UPDATE project_info SET session='{0}' WHERE ID={1}", session, projectid);

            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }

        public int QueryProjectID(string projectcode)
        {
            string strsql = "SELECT ID FROM vi_project WHERE IsDelete=0 and project_code='" + projectcode + "'";

            return SqlHelper<ProjectInfo>.SqlScalar(strsql);
        }

        public int QueryRoomID(int projectID, int roomID)
        {
            string strsql = "select ID FROM vi_room WHERE projectID=" + projectID + " AND ID=" + roomID;

            return SqlHelper<ProjectInfo>.SqlScalar(strsql);
        }
    }
}
